﻿Imports System.Data.OleDb
Imports DataSetHelper
Public Class frmThemPhieuNhapKho
    Private ChiTietPhieuNhapKho As New DataTable
    Private RowEditIndex As Integer
    Private bEdit As Boolean
#Region "Form Event"
    Public PhieuNhapKhoId As Integer = -1
    Public FormSender As String
    ''' <summary>
    ''' Thong Tin Khi Chuyen Tu Don Hang Sang Phieu Kho
    ''' </summary>
    ''' <remarks>KhachHangID gan vao bien nay lblKhachHangID.Text</remarks>
    Public Ngay As DateTime
    Public Cua As String
    Public KhachHangID As Integer
    Public So As Integer
    Public dtChiTietDonHang As DataTable
    Private Sub frmThemPhieuNhapKho_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            cmdThemLoaiPhieu.UseVisualStyleBackColor = False
            NamPhieuNhapKho_Load()
            CreateHeaderNewOrder()
            txtSoLuongThuc.Text = "0.00"
            txtSoLuongTheoCTu.Text = "0.00"
            txtDonGia.Text = "0.00"
            txtThanhTien.Text = "0.00"
            txtTenKhachHang.Text = ""
            lblKhachHangID.Text = "0"
            dtpNgay.Value = Now
            dtpNgayNhapKho.Value = Now
            txtGhiChu.Text = ""
            LoaiSanPham_Load()
            LoaiPhieuKho_Load()
            txtSo.Text = TaoSoPhieu(cbxTheoLenh.SelectedValue, "PhieuNhapKho")
            Kho_Load()
            bEdit = False
            If PhieuNhapKhoId <> -1 Then
                'phieu nhap kho
                PhieuNhapKho_Load_ById(PhieuNhapKhoId)
                'chi tiet phieu nhap kho
                ChiTietPhieuNhapKho_Load_ById(PhieuNhapKhoId)
            Else
                ChiTietPhieuNhapKho = Create_ChiTietPhieuNhapKho()
                txtHoTenNguoiNhanHang.Text = "Lê Duy Tấn"
            End If
            If FormSender = "XemDonHang" Then
                If LoaiPhieuID_HuyHoaDon = -1 Then
                    MessageBox.Show("Không tìm thấy loại phiếu: Hủy Hóa Đơn.Vui lòng kiểm tra lại thông tin!", "Thông báo", MessageBoxButtons.OK)
                Else
                    cbxTheoLenh.SelectedValue = LoaiPhieuID_HuyHoaDon
                End If
                cmdThemSanPham.Enabled = False
                cmdHuyBo.Enabled = False
                cmdEdit.Enabled = False
                cmdDelete.Enabled = False
                dtpNgay.Value = Ngay
                txtTenKhachHang.Text = Cua
                lblKhachHangID.Text = KhachHangID
                txtTenKhachHang.Enabled = False
                txtTenKhachHang.ReadOnly = True
                txtSo.Value = So

                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dbCommand As OleDb.OleDbCommand
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                For i As Integer = 0 To dtChiTietDonHang.Rows.Count - 1
                    If dtChiTietDonHang.Rows(i)("LoaiPhi") = "HangHoa" Then
                        Dim newRow As DataRow
                        newRow = ChiTietPhieuNhapKho.NewRow
                        newRow.Item("IDSanPham") = dtChiTietDonHang.Rows(i)("IdSanPham")
                        newRow.Item("MaSanPham") = dtChiTietDonHang.Rows(i)("MaSanPham")
                        newRow.Item("TenSanPham") = dtChiTietDonHang.Rows(i)("TenSanPham")
                        newRow.Item("DVTSanPham") = dtChiTietDonHang.Rows(i)("TenQuyCach")
                        ''

                        dbCommand = New OleDbCommand("sp_SanPham_DonViTinh", dbConn)
                        dbCommand.CommandType = CommandType.StoredProcedure
                        dbCommand.Parameters.Add("@IDSanPham", OleDbType.Integer).Value = dtChiTietDonHang.Rows(i)("IdSanPham")

                        newRow.Item("DVT") = dbCommand.ExecuteScalar().ToString()
                        dbCommand.Dispose()

                        newRow.Item("SoLuongTheoCTu") = dtChiTietDonHang.Rows(i)("SoLuong")
                        newRow.Item("SoLuongThuc") = 0
                        newRow.Item("DonGia") = 0
                        newRow.Item("ThanhTien") = 0
                        ChiTietPhieuNhapKho.Rows.Add(newRow)
                    End If
                Next
                dbConn.Close()
                BindDataNewOrder(ChiTietPhieuNhapKho)
            Else
                cmdThemSanPham.Enabled = True
                cmdHuyBo.Enabled = True
                cmdEdit.Enabled = True
                cmdDelete.Enabled = True
                txtTenKhachHang.Enabled = True
                txtTenKhachHang.ReadOnly = False
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Phiếu nhập kho mới"
    Private Sub CreateHeaderNewOrder()
        Try
            Grid1.ColumnsCount = 8
            Grid1.Rows.Clear()
            'tạo header 
            Grid1.FixedRows = 1
            Grid1.Rows.Insert(0)
            Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
            Dim fontBold As New Font(Grid1.Font, FontStyle.Bold)
            rowCOLSPAN.Font = fontBold
            Grid1(0, 0) = New SourceGrid.Cells.ColumnHeader("STT")
            Grid1(0, 0).Column.Width = 40
            Grid1(0, 0).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(0, 0).View.Font = Grid1.Font

            Grid1(0, 1) = New SourceGrid.Cells.ColumnHeader("TÊN SẢN PHẨM, QUY CÁCH SẢN PHẨM")
            Grid1(0, 1).Column.Width = 275
            Grid1(0, 1).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(0, 1).View.Font = Grid1.Font

            Grid1(0, 2) = New SourceGrid.Cells.ColumnHeader("ĐVT")
            Grid1(0, 2).Column.Width = 60
            Grid1(0, 2).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(0, 2).View.Font = Grid1.Font


            Grid1(0, 3) = New SourceGrid.Cells.ColumnHeader("SỐ LƯỢNG")
            Grid1(0, 3).Column.Width = 80
            Grid1(0, 3).ColumnSpan = 2
            Grid1(0, 3).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(0, 3).View.Font = Grid1.Font

            Grid1(0, 5) = New SourceGrid.Cells.ColumnHeader("Đơn giá")
            Grid1(0, 5).Column.Width = 80
            Grid1(0, 5).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(0, 5).View.Font = Grid1.Font

            Grid1(0, 6) = New SourceGrid.Cells.ColumnHeader("THÀNH TIỀN")
            Grid1(0, 6).Column.Width = 80
            Grid1(0, 6).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(0, 6).View.Font = Grid1.Font

            Grid1(0, 7) = New SourceGrid.Cells.ColumnHeader("IDSanPham")
            Grid1(0, 7).Column.Width = 0
            Grid1(0, 7).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(0, 7).View.Font = Grid1.Font

            Grid1.Rows.Insert(1)
            Grid1(1, 3) = New SourceGrid.Cells.ColumnHeader("Theo C.từ")
            Grid1(1, 3).Column.Width = 80
            Grid1(1, 3).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(1, 3).View.Font = Grid1.Font

            Grid1(1, 4) = New SourceGrid.Cells.ColumnHeader("Thực nhập")
            Grid1(1, 4).Column.Width = 80
            Grid1(1, 4).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(1, 4).View.Font = Grid1.Font

            Grid1(0, 0).RowSpan = 2
            Grid1(0, 1).RowSpan = 2
            Grid1(0, 2).RowSpan = 2
            Grid1(0, 5).RowSpan = 2
            Grid1(0, 6).RowSpan = 2
            Grid1(0, 7).RowSpan = 2
        Catch ex As Exception

        End Try
    End Sub
    Private Sub BindDataNewOrder(ByVal dtPhieuNhapKho As DataTable)
        Try
            'clear row
            If dtPhieuNhapKho.Rows.Count < 0 Then
                Exit Sub
            End If                        
            '/Grid1.Controller.AddController(New ValueChangedEvent)
            Grid1.Rows.Clear()
            CreateHeaderNewOrder()            
            Dim aTenSanPhamKhongTrung As New ArrayList            
            Dim aDVT As New ArrayList
            aTenSanPhamKhongTrung.Add(dtPhieuNhapKho.Rows(0)("TenSanPham"))
            aDVT.Add(dtPhieuNhapKho.Rows(0)("DVT"))
            For i As Integer = 1 To dtPhieuNhapKho.Rows.Count - 1
                If Not aTenSanPhamKhongTrung.Contains(dtPhieuNhapKho.Rows(i)("TenSanPham").ToString().Trim()) Then
                    aTenSanPhamKhongTrung.Add(dtPhieuNhapKho.Rows(i)("TenSanPham"))
                    aDVT.Add(dtPhieuNhapKho.Rows(i)("DVT"))
                End If
            Next
           
            Dim nextRowIndex As Integer = 2
            'group title
            Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
            Dim fontBold As New Font(Grid1.Font, FontStyle.Bold)
            rowCOLSPAN.Font = fontBold
            rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
            Dim rowHeader As New SourceGrid.Cells.Views.Cell
            rowHeader.Font = fontBold
            rowHeader.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            rowHeader.BackColor = Color.FromArgb(192, 255, 192)
            Dim cellID As New SourceGrid.Cells.Views.Cell
            cellID.ForeColor = Color.White
            Dim SumTongCTu As Single
            Dim SumTongThuc As Single
            For i As Integer = 0 To aTenSanPhamKhongTrung.Count - 1
                Grid1.Rows.Insert(nextRowIndex)
                Grid1(nextRowIndex, 0) = New SourceGrid.Cells.Cell(i + 1)
                Grid1(nextRowIndex, 0).View = rowHeader
                Grid1(nextRowIndex, 1) = New SourceGrid.Cells.Cell(aTenSanPhamKhongTrung(i))
                Grid1(nextRowIndex, 1).View = rowCOLSPAN
                Grid1(nextRowIndex, 2) = New SourceGrid.Cells.Cell(aDVT(i))
                Grid1(nextRowIndex, 2).View = rowHeader
                'Ô Tổng
                Dim currentHeaderIndex As Integer
                currentHeaderIndex = nextRowIndex
                Grid1(currentHeaderIndex, 3) = New SourceGrid.Cells.Cell("")
                Grid1(currentHeaderIndex, 3).View = rowHeader
                Grid1(nextRowIndex, 4) = New SourceGrid.Cells.Cell("")
                Grid1(nextRowIndex, 4).View = rowHeader
                Grid1(nextRowIndex, 5) = New SourceGrid.Cells.Cell("")
                Grid1(nextRowIndex, 5).View = rowCOLSPAN
                Grid1(nextRowIndex, 6) = New SourceGrid.Cells.Cell("")
                Grid1(nextRowIndex, 6).View = rowCOLSPAN
                Grid1(nextRowIndex, 7) = New SourceGrid.Cells.Cell("")
                Grid1(nextRowIndex, 7).View = rowCOLSPAN
                Dim rowDetail() As DataRow
                rowDetail = dtPhieuNhapKho.Select("TenSanPham='" & aTenSanPhamKhongTrung(i) & "'")
                nextRowIndex = nextRowIndex + 1
                Dim TongCongTheoCtu As Single = 0
                Dim TongCongThuc As Single = 0
                For j As Integer = 0 To rowDetail.Length - 1
                    Grid1.Rows.Insert(nextRowIndex)
                    Grid1(nextRowIndex, 0) = New SourceGrid.Cells.Cell(rowDetail(j)("IDSanPham"))
                    Grid1(nextRowIndex, 0).View = cellID
                    Dim SoLuongTheoCTu As Single
                    SoLuongTheoCTu = Single.Parse(rowDetail(j)("SoLuongTheoCTu"))
                    If SoLuongTheoCTu > 1 Then
                        SoLuongTheoCTu = Single.Parse(SoLuongTheoCTu)
                    End If
                    Grid1(nextRowIndex, 1) = New SourceGrid.Cells.Cell(SoLuongTheoCTu.ToString & "x" & rowDetail(j)("DVTSanPham"))
                    Grid1(nextRowIndex, 1).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
                    Grid1(nextRowIndex, 2) = New SourceGrid.Cells.Cell()
                    'tính toán ra số lượng
                    Dim sSoLuongTheoCTu As Single
                    sSoLuongTheoCTu = TinhSoLuongNhap(rowDetail(j)("IDSanPham"), rowDetail(j)("SoLuongTheoCTu"))
                    TongCongTheoCtu = TongCongTheoCtu + sSoLuongTheoCTu
                    'fix 
                    Grid1(nextRowIndex, 3) = New SourceGrid.Cells.Cell("") ')Format(sSoLuongTheoCTu, "0.00"))
                    Dim sSoLuongThuc As Single
                    sSoLuongThuc = TinhSoLuongNhap(rowDetail(j)("IDSanPham"), rowDetail(j)("SoLuongThuc"))
                    TongCongThuc = TongCongThuc + sSoLuongThuc
                    'fix
                    Grid1(nextRowIndex, 4) = New SourceGrid.Cells.Cell("")
                    If sSoLuongThuc > 0 Then
                        Grid1(nextRowIndex, 4).Value = "" 'Format(sSoLuongThuc, "0.00")
                    Else
                        Grid1(nextRowIndex, 4).Value = ""
                    End If
                    Grid1(nextRowIndex, 5) = New SourceGrid.Cells.Cell(Format(rowDetail(j)("DonGia"), "0.00"))
                    Grid1(nextRowIndex, 6) = New SourceGrid.Cells.Cell(Format(rowDetail(j)("ThanhTien"), "0.00"))
                    Grid1(nextRowIndex, 7) = New SourceGrid.Cells.Cell(rowDetail(j)("IDSanPham"))
                    nextRowIndex = nextRowIndex + 1
                Next
                Grid1(currentHeaderIndex, 3).Value = Convert.ToDouble(TongCongTheoCtu).ToString("N", VietNam).ToString() ' Format(TongCongTheoCtu, "0.00")
                SumTongCTu = SumTongCTu + TongCongTheoCtu
                If TongCongThuc > 0 Then
                    'Grid1(currentHeaderIndex, 4).Value = Format(TongCongThuc, "0.00")
                    Grid1(currentHeaderIndex, 4).Value = Convert.ToDouble(TongCongThuc).ToString("N", VietNam).ToString() ' Format(TongCongTheoCtu, "0.00")
                End If
                SumTongThuc = SumTongThuc + TongCongThuc
            Next
            'Insert Sum Row
            Grid1.Rows.Insert(nextRowIndex)
            Grid1(nextRowIndex, 0) = New SourceGrid.Cells.Cell("")
            Grid1(nextRowIndex, 0).ColumnSpan = 2
            Grid1(nextRowIndex, 0).View = rowCOLSPAN
            Grid1(nextRowIndex, 2) = New SourceGrid.Cells.Cell("Tổng")
            Grid1(nextRowIndex, 2).View = rowCOLSPAN
            'Tính Tổng Chứng Từ
            'Grid1(nextRowIndex, 3) = New SourceGrid.Cells.Cell(Format(SumTongCTu, "0.00"))
            Grid1(nextRowIndex, 3) = New SourceGrid.Cells.Cell(Convert.ToDouble(SumTongCTu).ToString("N", VietNam).ToString())
            Grid1(nextRowIndex, 3).View = rowCOLSPAN
            Grid1(nextRowIndex, 3).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            'Tính Tổng Thực
            If SumTongThuc > 0 Then
                'Grid1(nextRowIndex, 4) = New SourceGrid.Cells.Cell(Format(SumTongThuc, "0.00"))
                Grid1(nextRowIndex, 4) = New SourceGrid.Cells.Cell(Convert.ToDouble(SumTongThuc).ToString("N", VietNam).ToString())
            Else
                Grid1(nextRowIndex, 4) = New SourceGrid.Cells.Cell("")
            End If
            Grid1(nextRowIndex, 4).View = rowCOLSPAN
            Grid1(nextRowIndex, 4).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
            Grid1(nextRowIndex, 5) = New SourceGrid.Cells.Cell("")
            Grid1(nextRowIndex, 5).View = rowCOLSPAN
            Grid1(nextRowIndex, 6) = New SourceGrid.Cells.Cell("")
            Grid1(nextRowIndex, 6).View = rowCOLSPAN
        Catch ex As Exception

        End Try
    End Sub
    Private Function TinhSoLuongNhap(ByVal IDSanPham As String, ByVal SoLuongTheoCTu As Single) As Single
        Try
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            Dim SoLuong As Single
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dbCommand As OleDbCommand
            Dim query As String = "Select DVTSanPham.SoLuong From SanPham" & _
                                    " Left Outer Join DVTSanPham On DVTSanPham.DVTSanPhamId=SanPham.DVTSanPhamId" & _
                                    " Where SanPham.IDSanPham =" & IDSanPham

            dbCommand = New OleDbCommand(query, dbConn)
            If IsDBNull(dbCommand.ExecuteScalar) Then
                SoLuong = 0
            Else
                SoLuong = dbCommand.ExecuteScalar()
            End If
            dbConn.Close()
            Dim sResult As Single = SoLuong * SoLuongTheoCTu
            sResult = Format(sResult, "0.00")
            Return sResult
        Catch ex As Exception
            ShowError()
        End Try
    End Function
#End Region
#Region "Tạo Số Phiếu"
    Private Function TaoSoPhieu(ByVal LoaiPhieuID As Integer, ByVal LoaiPhieu As String) As Integer
        Try
            Dim SoPhieu As String = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dbCommand As OleDb.OleDbCommand
            'Them NamPhieuNhapKho
            Dim NamPhieuNhapKho As YearInfo
            NamPhieuNhapKho = CType(cbxNamPhieuNhapKho.SelectedItem, YearInfo)

            dataAccess.ConnectDatabase_DBAccess(dbConn)
            dbCommand = New OleDbCommand("spPhieuNhapKho_TangSoPhieu", dbConn)
            dbCommand.CommandType = CommandType.StoredProcedure
            dbCommand.Parameters.Add("@LoaiPhieuID", OleDbType.Integer).Value = LoaiPhieuID
            dbCommand.Parameters.Add("@LoaiPhieu", OleDbType.VarWChar).Value = LoaiPhieu
            dbCommand.Parameters.Add("@NamPhieuNhapKho", OleDbType.Integer).Value = NamPhieuNhapKho.Nam
            SoPhieu = dbCommand.ExecuteScalar()
            dbConn.Close()
            Return SoPhieu
        Catch ex As Exception

        End Try
    End Function
#End Region
    Private Sub NamPhieuNhapKho_Load()
        Dim currentYear As Integer = Now.Year
        cbxNamPhieuNhapKho.Items.Clear()
        Try
            While (currentYear >= 2011)
                cbxNamPhieuNhapKho.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                currentYear = currentYear - 1
            End While
            cbxNamPhieuNhapKho.SelectedIndex = 0
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Class YearInfo
        Dim _Nam As Integer
        Dim _TenNam As String
        Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
            _Nam = Nam
            _TenNam = TenNam
        End Sub
        Public Function Nam() As Integer
            Return _Nam
        End Function
        Public Function TenNam() As String
            Return _TenNam
        End Function
        Public Overrides Function ToString() As String
            Return _TenNam
        End Function
    End Class
#Region "Button Event"
    Private Sub txtMaLoai_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtHoTenNguoiNhanHang.KeyUp
        Try
            txtHoTenNguoiNhanHang.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtLoaiSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
        Try
            'txtTheoLenh.BackColor = Color.White
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Try
            If txtHoTenNguoiNhanHang.Text = "" Then
                EmptyData(txtHoTenNguoiNhanHang)
                Exit Sub
            End If
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            Dim dbCommand As OleDbCommand
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim query As String = ""
            If PhieuNhapKhoId <> -1 Then
                'roll back
                PhieuNhapKho_TruKho(PhieuNhapKhoId)
                query = "Update PhieuNhapKho Set Xoa=1,NgayXoa=N'" & NgayTao() & "',NguoiXoa='" & NguoiTao() & "'  Where PhieuNhapKhoId=" & PhieuNhapKhoId & ";Update ChiTietPhieuNhapKho Set Xoa=1,NgayXoa=N'" & NgayTao() & "',NguoiXoa='" & NguoiTao() & "'  Where PhieuNhapKhoId=" & PhieuNhapKhoId
                dbCommand = New OleDbCommand(query, dbConn)
                dbCommand.ExecuteNonQuery()
            End If
            Dim HoTenNguoiNhanHang As String = txtHoTenNguoiNhanHang.Text
            Dim TheoLenh As String = cbxTheoLenh.Text
            Dim So As String = IIf(txtSo.Text = "", 0, txtSo.Text)
            Dim Ngay As String = dtpNgay.Value.ToString.Split(" ")(0)
            Dim Cua As String = txtTenKhachHang.Text
            Dim KhachHangId As Integer = IIf(lblKhachHangID.Text = "", 0, Integer.Parse(lblKhachHangID.Text))
            Dim NhapKhoTai As String = cbxKho.Text
            Dim NgayNhapKho As String = dtpNgayNhapKho.Value.ToString.Split(" ")(0)
            Dim NgayNhap As String = dtpNgayNhapKho.Value.ToString.Split(" ")(0)
            Dim ThangNhap As String = NgayNhap.Split("/")(0)
            Dim NamNhap As String = NgayNhap.Split("/")(2)
            'fix NgayNhapSo = NgayNhapPhieu
            Dim NgayNhapSo As Integer = ConvertDateToInt(NgayNhapKho)
            'Them NamPhieuNhapKho
            Dim NamPhieuNhapKho As YearInfo
            NamPhieuNhapKho = CType(cbxNamPhieuNhapKho.SelectedItem, YearInfo)

            query = String.Format("Insert Into PhieuNhapKho(HoTenNguoiNhanHang,TheoLenh,So,Ngay,Cua,NhapKhoTai,NgayNhapKho,ThangNhap,NamNhap,NgayNhapBangSo,LoaiPhieu,KhachHangId,TrangThai,NgayTao,NguoiTao,LoaiPhieuID,KhoID,GhiChu,IDLoai,TenLoai,NamPhieuNhapKho) Values(N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}',N'{6}',N'{7}',N'{8}',{9},N'{10}',{11},N'{12}',N'{13}',N'{14}',{15},N'{16}',N'{17}',{18},N'{19}',{20})", HoTenNguoiNhanHang, TheoLenh, So, Ngay, Cua, NhapKhoTai, NgayNhap, ThangNhap, NamNhap, NgayNhapSo, "PhieuNhapKho", KhachHangId, "Đã duyệt", NgayTao, NguoiTao, cbxTheoLenh.SelectedValue, cbxKho.SelectedValue, txtGhiChu.Text, cbxLoai.SelectedValue, cbxLoai.Text, NamPhieuNhapKho.Nam)
            dbCommand = New OleDbCommand(query, dbConn)
            dbCommand.ExecuteNonQuery()
            'luu chi tiet hoa don nhap
            Dim PhieuNhapKhoId_Latest As Integer
            query = "Select Max(PhieuNhapKhoId) From PhieuNhapKho"
            dbCommand = New OleDbCommand(query, dbConn)
            PhieuNhapKhoId_Latest = dbCommand.ExecuteScalar()
            Dim IDSanPham As String
            Dim MaSanPham As String
            Dim TenSanPham As String
            Dim DVTSanPham As String
            Dim DVT As String
            Dim SoLuongTheoCTu As String
            Dim SoLuongThuc As String
            Dim DonGia As String
            Dim ThanhTien As String
            For i As Integer = 0 To ChiTietPhieuNhapKho.Rows.Count - 1
                'tao phieu
                IDSanPham = ChiTietPhieuNhapKho.Rows(i)("IDSanPham")
                MaSanPham = ChiTietPhieuNhapKho.Rows(i)("MaSanPham")
                TenSanPham = ChiTietPhieuNhapKho.Rows(i)("TenSanPham")
                DVTSanPham = ChiTietPhieuNhapKho.Rows(i)("DVTSanPham")
                DVT = ChiTietPhieuNhapKho.Rows(i)("DVT")
                SoLuongTheoCTu = Single.Parse(ChiTietPhieuNhapKho.Rows(i)("SoLuongTheoCTu"))
                SoLuongThuc = Single.Parse(ChiTietPhieuNhapKho.Rows(i)("SoLuongThuc"))                                                                                                                                                                                             'PhieuNhapKhoId,IDSanPham,MaSanPham,TenSanPham,DVTSanPham,DVT,SoLuongTheoCTu,SoLuongThuc,NgayNhapPhieu,ThangNhap,NamNhap,NgayNhapTheoSo,LoaiPhieu,KhachHangId,TrangThai,NgayTao,NguoiTao
                DonGia = Single.Parse(ChiTietPhieuNhapKho.Rows(i)("DonGia"))
                ThanhTien = Single.Parse(ChiTietPhieuNhapKho.Rows(i)("ThanhTien"))
                query = String.Format("Insert Into ChiTietPhieuNhapKho(PhieuNhapKhoId,IDSanPham,MaSanPham,TenSanPham,DVTSanPham,DVT,SoLuongTheoCTu,SoLuongThuc,NgayNhapPhieu,ThangNhap,NamNhap,NgayNhapTheoSo,LoaiPhieu,KhachHangId,TrangThai,NgayTao,NguoiTao,DonGia,ThanhTien,So) Values({0},{1},N'{2}',N'{3}',N'{4}',N'{5}',{6},{7},N'{8}',N'{9}',N'{10}',{11},N'{12}',{13},N'{14}',N'{15}',N'{16}',{17},{18},N'{19}')", PhieuNhapKhoId_Latest, IDSanPham, MaSanPham, TenSanPham, DVTSanPham, DVT, Single.Parse(SoLuongTheoCTu, Globalization.NumberStyles.Currency), Single.Parse(SoLuongThuc, Globalization.NumberStyles.Currency), NgayNhapKho, ThangNhap, NamNhap, NgayNhapSo, "PhieuNhapKho", KhachHangId, "Đã duyệt", NgayTao, NguoiTao, DonGia, ThanhTien, txtSo.Text.Trim)
                dbCommand = New OleDbCommand(query, dbConn)
                dbCommand.ExecuteNonQuery()
            Next
            txtTenSanPham.Text = ""
            txtDonViTinh.Text = ""
            txtSoLuongThuc.Text = "0.00"
            txtSoLuongTheoCTu.Text = "0.00"
            txtDonGia.Text = "0.00"
            txtThanhTien.Text = "0.00"
            txtSo.Text = TaoSoPhieu(cbxTheoLenh.SelectedValue, "PhieuNhapKho")
            txtTenKhachHang.Text = ""
            lblKhachHangID.Text = "0"
            txtGhiChu.Text = ""
            txtHoTenNguoiNhanHang.Text = "Lê Duy Tấn"
            ChiTietPhieuNhapKho = Create_ChiTietPhieuNhapKho()
            PhieuNhapKho_CongKho()
            query = "Update PhieuNhapKho Set TrangThai=N'Đã duyệt' Where PhieuNhapKhoId=" & PhieuNhapKhoId_Latest & ";Update ChiTietPhieuNhapKho Set TrangThai=N'Đã duyệt' Where PhieuNhapKhoId=" & PhieuNhapKhoId_Latest
            dbCommand = New OleDbCommand(query, dbConn)
            dbCommand.ExecuteNonQuery()
            dbConn.Close()
            'clear grid
            Grid1.Rows.Clear()
            CreateHeaderNewOrder()
            If PhieuNhapKhoId <> -1 Then
                PhieuNhapKhoId = -1
                Me.Close()
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub PhieuNhapKho_TruKho(ByVal _PhieuNhapKhoID As String)
        Try
            'kiem tra tinh trang
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dbCommand As OleDbCommand
            Dim query As String = "Select KhoID From PhieuNhapKho Where PhieuNhapKhoId=" & _PhieuNhapKhoID
            dbCommand = New OleDbCommand(query, dbConn)
            Dim KhoID As String = dbCommand.ExecuteScalar()
            'tru kho
            Dim dtPhieuNhapKho As New DataTable
            query = "Select SoLuongTheoCtu,IDSanPham from ChiTietPhieuNhapKho Where PhieuNhapKhoId=" & _PhieuNhapKhoID
            Dim adapter As OleDb.OleDbDataAdapter
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtPhieuNhapKho)
            Dim SoLuongThuc As Single
            Dim IDSanPham As Integer
            Dim UpDateSoLuongTon As String = ""
            If KhoID = "KhoThanhPham" Then
                UpDateSoLuongTon = "Update SanPham Set SoLuongTon=SoLuongTon - "
            ElseIf KhoID = "KhoXuLy" Then
                UpDateSoLuongTon = "Update SanPham Set SoLuongKhoXuLy=SoLuongKhoXuLy - "
            End If
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                SoLuongThuc = Single.Parse(dtPhieuNhapKho.Rows(i)("SoLuongTheoCtu"))
                IDSanPham = Integer.Parse(dtPhieuNhapKho.Rows(i)("IDSanPham"))
                query = UpDateSoLuongTon & SoLuongThuc & " Where IdSanPham=" & IDSanPham
                dbCommand = New OleDbCommand(query, dbConn)
                dbCommand.ExecuteNonQuery()
            Next
            dbConn.Close()
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub PhieuNhapKho_CongKho()
        Try
            'kiem tra tinh trang
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dbCommand As OleDbCommand
            'Lay Phieu Moi Nhat
            Dim query As String
            query = "Select Max(PhieuNhapKhoId) From PhieuNhapKho"
            dbCommand = New OleDbCommand(query, dbConn)
            Dim PhieuNhapKho As Integer
            PhieuNhapKho = dbCommand.ExecuteScalar()
            query = "Select KhoID From PhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKho
            dbCommand = New OleDbCommand(query, dbConn)
            Dim KhoID As String = dbCommand.ExecuteScalar()
            'If TrangThai = "Đã duyệt" Then
            '    'MessageBox.Show("Phiếu đã duyệt.Bạn không thể duyệt được nữa", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            '    dbConn.Close()
            '    Exit Sub
            'Else
            query = "Update PhieuNhapKho Set TrangThai=N'Đã duyệt' Where PhieuNhapKhoId=" & PhieuNhapKho & ";Update ChiTietPhieuNhapKho Set TrangThai=N'Đã duyệt' Where PhieuNhapKhoId=" & PhieuNhapKho
            dbCommand = New OleDbCommand(query, dbConn)
            dbCommand.ExecuteNonQuery()
            'nhap kho
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            query = "Select SoLuongTheoCtu,IDSanPham from ChiTietPhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKho
            Dim adapter As OleDb.OleDbDataAdapter
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            Dim SoLuongThuc As Single
            Dim IDSanPham As Integer
            Dim UpDateSoLuongTon As String = ""
            If KhoID = "KhoThanhPham" Then
                UpDateSoLuongTon = "Update SanPham Set SoLuongTon=SoLuongTon + "
            ElseIf KhoID = "KhoXuLy" Then
                UpDateSoLuongTon = "Update SanPham Set SoLuongKhoXuLy=SoLuongKhoXuLy + "
            End If
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                SoLuongThuc = Single.Parse(dtPhieuNhapKho.Rows(i)("SoLuongTheoCtu"))
                IDSanPham = Integer.Parse(dtPhieuNhapKho.Rows(i)("IDSanPham"))
                query = UpDateSoLuongTon & SoLuongThuc & " Where IdSanPham=" & IDSanPham
                dbCommand = New OleDbCommand(query, dbConn)
                dbCommand.ExecuteNonQuery()
            Next
            dbConn.Close()
            'End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
        Try
            txtTenSanPham.Text = ""
            txtDonViTinh.Text = ""
            txtSoLuongThuc.Text = "0.00"
            txtSoLuongTheoCTu.Text = "0.00"
            txtSo.Text = TaoSoPhieu(cbxTheoLenh.SelectedValue, "PhieuNhapKho")
            txtTenKhachHang.Text = ""
            lblKhachHangID.Text = "0"
            txtHoTenNguoiNhanHang.Text = "Lê Duy Tấn"
            txtGhiChu.Text = ""
            'clear sản phẩm d0
            ChiTietPhieuNhapKho = Create_ChiTietPhieuNhapKho()
            If PhieuNhapKhoId <> -1 Then
                PhieuNhapKhoId = -1
            End If
            Me.Close()
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Load Data"
    Public Sub LoaiSanPham_Load()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtLoaiSanPham As New DataTable
            Dim query As String = "Select IDLoai,MaLoai From LoaiSanPham Order By TenLoai ASC"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtLoaiSanPham)
            dbConn.Close()
            cbxLoai.DataSource = dtLoaiSanPham
            cbxLoai.DisplayMember = "MaLoai"
            cbxLoai.ValueMember = "IDLoai"
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub Kho_Load()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtLoaiPhieuKho As New DataTable
            Dim query As String = "Select KhoID,TenKho From Kho Where Xoa=0"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtLoaiPhieuKho)
            dbConn.Close()
            cbxKho.DataSource = dtLoaiPhieuKho
            cbxKho.DisplayMember = "TenKho"
            cbxKho.ValueMember = "KhoID"
        Catch ex As Exception

        End Try
    End Sub
    Private LoaiPhieuID_HuyHoaDon As Integer = -1
    Private Sub LoaiPhieuKho_Load()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtLoaiPhieuKho As New DataTable
            Dim query As String = "Select LoaiPhieuID,TenPhieu,LoaiPhieu From LoaiPhieu Where LoaiPhieu='PhieuNhap' And Xoa=0"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtLoaiPhieuKho)
            dbConn.Close()
            cbxTheoLenh.DataSource = dtLoaiPhieuKho
            cbxTheoLenh.DisplayMember = "TenPhieu"
            cbxTheoLenh.ValueMember = "LoaiPhieuID"

            For i As Integer = 0 To dtLoaiPhieuKho.Rows.Count
                If dtLoaiPhieuKho.Rows(i)("TenPhieu") = "Hủy Hóa Đơn" And dtLoaiPhieuKho.Rows(i)("LoaiPhieu") = "PhieuNhap" Then
                    LoaiPhieuID_HuyHoaDon = dtLoaiPhieuKho.Rows(i)("LoaiPhieuID")
                End If
            Next

        Catch ex As Exception

        End Try
    End Sub
    Private Sub PhieuNhapKho_Load_ById(ByVal PhieuNhapKhoId As Integer)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            Dim query As String = "Select * from PhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKhoId
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dbConn.Close()
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            txtHoTenNguoiNhanHang.Text = dtPhieuNhapKho.Rows(0)("HoTenNguoiNhanHang")
            cbxTheoLenh.SelectedValue = dtPhieuNhapKho.Rows(0)("LoaiPhieuID")
            txtSo.Text = dtPhieuNhapKho.Rows(0)("So")
            dtpNgay.Value = dtPhieuNhapKho.Rows(0)("Ngay")
            txtTenKhachHang.Text = dtPhieuNhapKho.Rows(0)("Cua")
            lblKhachHangID.Text = dtPhieuNhapKho.Rows(0)("KhachHangID")
            cbxKho.SelectedValue = dtPhieuNhapKho.Rows(0)("KhoID")
            cbxLoai.SelectedValue = dtPhieuNhapKho.Rows(0)("IDLoai")
            dtpNgayNhapKho.Value = dtPhieuNhapKho.Rows(0)("NgayNhapKho")
            txtGhiChu.Text = IIf(IsDBNull(dtPhieuNhapKho.Rows(0)("GhiChu")), "", dtPhieuNhapKho.Rows(0)("GhiChu"))
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub ChiTietPhieuNhapKho_Load_ById(ByVal PhieuNhapKhoId As Integer)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            Dim query As String = "Select * from ChiTietPhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKhoId
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dbConn.Close()
            'tao chi tiet phieu nhap kho
            ChiTietPhieuNhapKho = Create_ChiTietPhieuNhapKho()
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                Dim newRow As DataRow
                newRow = ChiTietPhieuNhapKho.NewRow
                newRow.Item("IDSanPham") = dtPhieuNhapKho.Rows(i)("IDSanPham")
                newRow.Item("MaSanPham") = dtPhieuNhapKho.Rows(i)("MaSanPham")
                newRow.Item("TenSanPham") = dtPhieuNhapKho.Rows(i)("TenSanPham")
                newRow.Item("DVTSanPham") = dtPhieuNhapKho.Rows(i)("DVTSanPham")
                newRow.Item("DVT") = dtPhieuNhapKho.Rows(i)("DVT")
                newRow.Item("SoLuongTheoCTu") = dtPhieuNhapKho.Rows(i)("SoLuongTheoCTu")
                newRow.Item("SoLuongThuc") = dtPhieuNhapKho.Rows(i)("SoLuongThuc")
                newRow.Item("DonGia") = dtPhieuNhapKho.Rows(i)("DonGia")
                newRow.Item("ThanhTien") = dtPhieuNhapKho.Rows(i)("ThanhTien")
                ChiTietPhieuNhapKho.Rows.Add(newRow)
            Next
            BindDataNewOrder(ChiTietPhieuNhapKho)
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region

#Region "Thêm Sản Phẩm Vào Chi Tiết Hóa Đơn"
    Protected Function Create_ChiTietPhieuNhapKho() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim PhieuNhapKhoId As DataColumn = New DataColumn("PhieuNhapKhoId")
            PhieuNhapKhoId.DataType = System.Type.GetType("System.Int32")
            dtTable.Columns.Add(PhieuNhapKhoId)

            Dim IDSanPham As DataColumn = New DataColumn("IDSanPham")
            IDSanPham.DataType = System.Type.GetType("System.Int32")
            dtTable.Columns.Add(IDSanPham)

            Dim STT As DataColumn = New DataColumn("STT")
            STT.DataType = System.Type.GetType("System.String")
            STT.DefaultValue = ""
            dtTable.Columns.Add(STT)

            Dim MaSanPham As DataColumn = New DataColumn("MaSanPham")
            MaSanPham.DataType = System.Type.GetType("System.String")
            MaSanPham.DefaultValue = ""
            dtTable.Columns.Add(MaSanPham)

            Dim TenSanPham As DataColumn = New DataColumn("TenSanPham")
            TenSanPham.DataType = System.Type.GetType("System.String")
            TenSanPham.DefaultValue = ""
            dtTable.Columns.Add(TenSanPham)

            Dim DVT As DataColumn = New DataColumn("DVTSanPham")
            DVT.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(DVT)

            Dim QuyCach As DataColumn = New DataColumn("DVT")
            QuyCach.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(QuyCach)

            Dim SoLuongTheoCTu As DataColumn = New DataColumn("SoLuongTheoCTu")
            SoLuongTheoCTu.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(SoLuongTheoCTu)

            Dim SoLuongThuc As DataColumn = New DataColumn("SoLuongThuc")
            SoLuongThuc.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(SoLuongThuc)

            Dim NgayNhapPhieu As DataColumn = New DataColumn("NgayNhapPhieu")
            NgayNhapPhieu.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(NgayNhapPhieu)

            Dim NgayNhap As DataColumn = New DataColumn("NgayNhap")
            NgayNhap.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(NgayNhap)

            Dim ThangNhap As DataColumn = New DataColumn("ThangNhap")
            ThangNhap.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(ThangNhap)

            Dim NamNhap As DataColumn = New DataColumn("NamNhap")
            NamNhap.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(NamNhap)

            Dim NgayNhapTheoSo As DataColumn = New DataColumn("NgayNhapTheoSo")
            NgayNhapTheoSo.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(NgayNhapTheoSo)

            Dim DonGia As DataColumn = New DataColumn("DonGia")
            DonGia.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(DonGia)

            Dim ThanhTien As DataColumn = New DataColumn("ThanhTien")
            ThanhTien.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(ThanhTien)

            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
    Private rowInserted As Integer = 2
    Private Sub cmdThemSanPham_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThemSanPham.Click
        Try
            Dim IDSanPham As Integer
            Dim MaSanPham As String
            Dim TenSanPham As String
            Dim DVTSanPham As String
            Dim DVT As String
            Dim SoLuongTheoCTu As String
            Dim SoLuongThuc As String
            Dim DonGia As String
            Dim ThanhTien As String
            IDSanPham = KiemTraTonTaiSanPham(txtTenSanPham.Text)
            If txtSoLuongTheoCTu.Text = "" Then
                txtSoLuongTheoCTu.BackColor = Color.FromArgb(192, 255, 255)
                txtSoLuongTheoCTu.Focus()
                Exit Sub
            End If
            If txtSoLuongThuc.Text = "" Then
                txtSoLuongThuc.Text = "0.00"
            End If
            If txtDonGia.Text = "" Then
                txtDonGia.Text = "0.00"
            End If
            If txtThanhTien.Text = "" Then
                txtThanhTien.Text = "0.00"
            End If
            If IDSanPham = 0 Then
                MessageBox.Show("Tên sản phẩm không có trong danh mục.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                EmptyData(txtTenSanPham)
                Exit Sub
            Else
                If bEdit Then
                    Dim rowUpdate() As DataRow
                    rowUpdate = ChiTietPhieuNhapKho.Select("IDSanPham=" & IDSanPham)
                    If rowUpdate.Length > 0 Then
                        rowUpdate(0).Item("SoLuongTheoCTu") = txtSoLuongTheoCTu.Text
                        rowUpdate(0).Item("SoLuongThuc") = txtSoLuongThuc.Text
                        rowUpdate(0).Item("DonGia") = txtDonGia.Text
                        rowUpdate(0).Item("ThanhTien") = txtThanhTien.Text
                        ChiTietPhieuNhapKho.AcceptChanges()
                    End If
                Else
                    'check IdSanPham Exist
                    'check 10 row
                    If Grid1.RowsCount > 13 Then
                        MessageBox.Show("Số dòng nhập tối đa trong một phiếu nhập là 12.Xin vui lòng kiểm tra lại", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Exit Sub
                    End If
                    Dim rowExist() As DataRow
                    rowExist = ChiTietPhieuNhapKho.Select("IDSanPham=" & IDSanPham)
                    If rowExist.Length > 0 Then
                        Dim SoLuongTheoCTuCu As Single
                        Dim SoLuongThucCu As Single
                        SoLuongTheoCTuCu = Single.Parse(rowExist(0).Item("SoLuongTheoCTu"))
                        SoLuongThucCu = Single.Parse(rowExist(0).Item("SoLuongThuc"))
                        'update
                        rowExist(0).Item("SoLuongTheoCTu") = SoLuongTheoCTuCu + Single.Parse(txtSoLuongTheoCTu.Text.Trim)
                        rowExist(0).Item("SoLuongThuc") = SoLuongThucCu + Single.Parse(txtSoLuongThuc.Text.Trim)
                        ChiTietPhieuNhapKho.AcceptChanges()
                    Else
                        Dim dbConn As New OleDb.OleDbConnection
                        Dim dataAccess As New DataAccess
                        Dim dsSanPham As New DataSet
                        Dim dtSanPham As New DataTable
                        Dim query As String = "Select LoaiSanPham.DonViTinh as DVTTheoLoai, SanPham.IDSanPham,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi From SanPham " & _
                                                " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                                " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                                " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                                " Where SanPham.Xoa=0 And SanPham.IDSanPham=" & IDSanPham
                        Dim adapter As OleDb.OleDbDataAdapter
                        dataAccess.ConnectDatabase_DBAccess(dbConn)
                        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                        adapter.Fill(dsSanPham)
                        dbConn.Close()
                        dtSanPham = dsSanPham.Tables(0)
                        IDSanPham = dtSanPham.Rows(0)("IDSanPham")
                        MaSanPham = dtSanPham.Rows(0)("MaSanPham")
                        TenSanPham = dtSanPham.Rows(0)("TenSanPham").ToString().Trim()
                        DVTSanPham = dtSanPham.Rows(0)("TenDonViTinh")
                        'DVT = dtSanPham.Rows(0)("TenDonVi")
                        'sua don vi tinh theo loa
                        DVT = dtSanPham.Rows(0)("DVTTheoLoai")
                        SoLuongTheoCTu = txtSoLuongTheoCTu.Text
                        SoLuongThuc = IIf(txtSoLuongThuc.Text = "", "0.00", txtSoLuongThuc.Text)
                        DonGia = IIf(txtDonGia.Text = "", "0.00", txtDonGia.Text)
                        ThanhTien = IIf(txtThanhTien.Text = "", "0.00", txtThanhTien.Text)
                        Dim newRow As DataRow
                        newRow = ChiTietPhieuNhapKho.NewRow
                        newRow.Item("IDSanPham") = IDSanPham
                        newRow.Item("MaSanPham") = MaSanPham
                        newRow.Item("TenSanPham") = TenSanPham
                        newRow.Item("DVTSanPham") = DVTSanPham

                        newRow.Item("DVT") = DVT

                        newRow.Item("SoLuongTheoCTu") = SoLuongTheoCTu
                        newRow.Item("SoLuongThuc") = SoLuongThuc
                        newRow.Item("DonGia") = DonGia
                        newRow.Item("ThanhTien") = ThanhTien
                        ChiTietPhieuNhapKho.Rows.Add(newRow)
                    End If
                End If
            End If
            'bind data
            BindDataNewOrder(ChiTietPhieuNhapKho)
            txtTenSanPham.Text = ""
            txtDonViTinh.Text = ""
            txtSoLuongThuc.Text = "0.00"
            txtSoLuongTheoCTu.Text = "0.00"
            txtDonGia.Text = "0.00"
            txtThanhTien.Text = "0.00"
            txtTenSanPham.Enabled = True
            txtDonViTinh.Enabled = True
            bEdit = False
            txtTenSanPham.Focus()
            'End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Function KiemTraTonTaiSanPham(ByVal TenSanPham As String) As Integer
        Try
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dbCommand As OleDbCommand
            Dim query As String = "Select IDSanPham From SanPham Where Xoa=0 And MaSanPham=N'" & TenSanPham & "'"
            dbCommand = New OleDbCommand(query, dbConn)
            If IsDBNull(dbCommand.ExecuteScalar) Then
                Return 0
            Else
                Return dbCommand.ExecuteScalar()
            End If
            dbConn.Close()
        Catch ex As Exception
            ShowError()
        End Try
    End Function
#End Region

#Region "Search San pham"
    Private Sub SanPham_Search(ByVal KeyWord As String)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                    " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                    " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                    " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                    " Where SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham_Search.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                'chú giải
            Next

            dgvSanPham_Search.DataSource = dtSanPham
            If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                dgvSanPham_Search.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                Dim TenSanPham As String
                Dim DVT As String
                TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                txtTenSanPham.Text = TenSanPham
                txtDonViTinh.Text = DVT
                'hide search panel
                PanelSanPham_Search.Visible = False
                txtSoLuongTheoCTu.Focus()
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception

        End Try
    End Sub



    Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
        Try
            If txtTenSanPham.Text <> "" Then
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgvSanPham_Search.Focus()
                End If
            End If
            txtTenSanPham.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvSanPham_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvSanPham_Search.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    PanelSanPham_Search.Visible = True
                Case Keys.Up
                    PanelSanPham_Search.Visible = True
                Case Keys.Enter
                    Dim TenSanPham As String
                    Dim DVT As String
                    TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                    DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                    txtTenSanPham.Text = TenSanPham
                    txtDonViTinh.Text = DVT
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                    txtSoLuongTheoCTu.Focus()
                    PanelSanPham_Search.Visible = False
                Case Else
                    txtTenSanPham.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtTenSanPham_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPham.TextChanged
        Try
            If txtTenSanPham.Text <> "" Then
                'show search panel
                If Not bEdit Then
                    PanelSanPham_Search.Visible = True
                    SanPham_Search(txtTenSanPham.Text)
                Else
                    PanelSanPham_Search.Visible = False
                End If
            Else
                'hide search panel
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
        Try
            'Gan Du Lieu
            Dim TenSanPham As String
            Dim DVT As String
            TenSanPham = dgvSanPham_Search.Rows(e.RowIndex).Cells("MaSanPham").Value.ToString
            DVT = dgvSanPham_Search.Rows(e.RowIndex).Cells("TenDonViTinh").Value.ToString
            txtTenSanPham.Text = TenSanPham
            txtDonViTinh.Text = DVT
            'hide search panel
            PanelSanPham_Search.Visible = False
            txtSoLuongTheoCTu.Focus()
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub txtDonViTinh_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDonViTinh.Enter
        Try
            PanelSanPham_Search.Visible = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub txtSoLuongTheoCTu_Enter(ByVal sender As Object, ByVal e As System.EventArgs)
        Try
            PanelSanPham_Search.Visible = False
        Catch ex As Exception

        End Try
    End Sub
    Private Sub txtSoLuongTheoCTu_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        Try
            If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
                e.Handled = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtSoLuongThuc_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        Try
            If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
                e.Handled = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub txtSoLuongTheoCTu_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
        Try
            txtSoLuongThuc.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtSoLuongThuc_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
        Try
            txtSoLuongTheoCTu.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvChiTietSanPhamNhap_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs)
        Try
            RowEditIndex = e.RowIndex
        Catch ex As Exception

        End Try
    End Sub
#End Region
    Private Sub cmdHuyBo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdHuyBo.Click
        Try
            txtTenSanPham.Text = ""
            txtDonViTinh.Text = ""
            txtSoLuongThuc.Text = "0.00"
            txtSoLuongTheoCTu.Text = "0.00"
            txtDonGia.Text = "0.00"
            txtThanhTien.Text = "0.00"
            bEdit = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub cmdDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDong.Click
        Try
            PanelSanPham_Search.Visible = False
        Catch ex As Exception

        End Try
    End Sub


    Private ColumnIndex As Integer = -1
    Private RowIndex As Integer = -1
    Private Sub Grid1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Grid1.Click
        Try
            Dim cellSelected As SourceGrid.Selection.FreeSelection
            cellSelected = Grid1.Selection()
            ColumnIndex = cellSelected.ActivePosition.Column
            RowIndex = cellSelected.ActivePosition.Row
        Catch ex As Exception

        End Try
    End Sub
    Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
        Try
            '2,3,4 --> 5,6,7
            Dim IDSanPham As Integer
            Dim rowUpdate() As DataRow
            If RowIndex <> -1 And ColumnIndex <> -1 Then
                bEdit = True
                IDSanPham = Grid1(RowIndex, 7).Value
                rowUpdate = ChiTietPhieuNhapKho.Select("IDSanPham=" & IDSanPham)
                txtTenSanPham.Text = rowUpdate(0)("MaSanPham")
                txtDonViTinh.Text = rowUpdate(0)("DVTSanPham")
                txtSoLuongTheoCTu.Text = rowUpdate(0)("SoLuongTheoCTu")
                txtSoLuongThuc.Text = rowUpdate(0)("SoLuongThuc")
                txtDonGia.Text = rowUpdate(0)("DonGia")
                txtThanhTien.Text = rowUpdate(0)("ThanhTien")
            End If
        Catch ex As Exception
            bEdit = False
        End Try
    End Sub
    Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Try
            Dim IDSanPham As Integer
            If RowIndex <> -1 And ColumnIndex <> -1 Then
                IDSanPham = Grid1(RowIndex, 7).Value
                Dim rowDelete() As DataRow
                rowDelete = ChiTietPhieuNhapKho.Select("IDSanPham=" & IDSanPham)
                rowDelete(0).Delete()
                ChiTietPhieuNhapKho.AcceptChanges()
                BindDataNewOrder(ChiTietPhieuNhapKho)
            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub cmdThemLoaiPhieu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThemLoaiPhieu.Click
        Try
            frmPhieuKho.LoaiPhieu = "PhieuNhap"
            ShowModalForm(frmPhieuKho, False, False)
            frmPhieuKho.LoaiPhieu = ""
        Catch ex As Exception

        End Try
    End Sub
#Region "Tìm kiếm khách hàng"
    Private Sub txtTenKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                lblKhachHangID.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                '---------Lấy Thống Tin Khách Hàng----------
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsTinhThanh As New DataSet
                Dim dtTinhThanh As New DataTable
                Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangID.Text
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsTinhThanh)

                dtTinhThanh = dsTinhThanh.Tables(0)
                '
                txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                Panel_KhachHang.Visible = False
                dbConn.Close()
                cbxKho.Focus()
            ElseIf e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then

            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub txtTenKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyUp
        Try
            If txtTenKhachHang.Text <> "" Then
                KhachHang_Quick_Search()
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgv_KhachHang_QuickSearch.Focus()
                    Panel_KhachHang.Visible = True
                End If
            Else
                Panel_KhachHang.Visible = False
                lblKhachHangID.Text = "0"
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Sub txtTenKhachHang_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTenKhachHang.Leave
        Try
            Panel_KhachHang.Visible = False
        Catch ex As Exception

        End Try
    End Sub
    Sub KhachHang_Quick_Search()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsKhachHang As New DataSet
            Dim dtKhachHang As New DataTable
            Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang.Text.Trim & "%'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsKhachHang)
            dbConn.Close()
            dtKhachHang = dsKhachHang.Tables(0)
            Panel_KhachHang.Visible = True
            dgv_KhachHang_QuickSearch.AutoGenerateColumns = False
            dgv_KhachHang_QuickSearch.DataSource = dtKhachHang
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Sub dgv_KhachHang_QuickSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgv_KhachHang_QuickSearch.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    Panel_KhachHang.Visible = True
                Case Keys.Up
                    Panel_KhachHang.Visible = True
                Case Keys.Enter
                    lblKhachHangID.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                    '---------Lấy Thống Tin Khách Hàng----------
                    Dim dbConn As New OleDb.OleDbConnection
                    Dim dataAccess As New DataAccess
                    Dim dsTinhThanh As New DataSet
                    Dim dtTinhThanh As New DataTable
                    Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangID.Text
                    Dim adapter As OleDb.OleDbDataAdapter
                    dataAccess.ConnectDatabase_DBAccess(dbConn)
                    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                    adapter.Fill(dsTinhThanh)
                    dtTinhThanh = dsTinhThanh.Tables(0)
                    '
                    txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                    '
                    Panel_KhachHang.Visible = False
                    dbConn.Close()
                Case Else
                    txtTenKhachHang.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub
#End Region

    Private Sub cbxTheoLenh_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbxTheoLenh.SelectionChangeCommitted
        Try
            txtSo.Text = TaoSoPhieu(cbxTheoLenh.SelectedValue, "PhieuNhapKho")
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cbxNamPhieuNhapKho_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxNamPhieuNhapKho.SelectionChangeCommitted
        Try
            txtSo.Text = TaoSoPhieu(cbxTheoLenh.SelectedValue, "PhieuNhapKho")
        Catch ex As Exception

        End Try
    End Sub
End Class